﻿IF EXISTS (SELECT *  FROM sys.objects  WHERE object_id = OBJECT_ID(N'fn_SplitAndSearch') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION fn_SplitAndSearch
GO

CREATE FUNCTION [dbo].[fn_SplitAndSearch]
(
	@text nvarchar(MAX),
	@delimiter char(1),
	@index int
)
RETURNS nvarchar(MAX)
AS
BEGIN
/*
	declare @value nvarchar(max)	
	select @value = Data.Value from dbo.fn_Split(@text, @delimiter) as Data where Data.Position = @index
	return @value
*/

	DECLARE @nextString NVARCHAR(MAX)
	DECLARE @position INT
	DECLARE @nextPosition INT
	DECLARE @delimiterCheck NVARCHAR(1)
	DECLARE @delimiterCount INT 
	 
	--Initialize
	SET @nextString = ''
	SET @delimiterCheck = RIGHT(@text,1) 
	SET @delimiterCount = 0
	
	--Check for trailing delimiter, if not exists
	IF (@delimiterCheck <> @delimiter )
	BEGIN
		SET @text = @text + @delimiter
	END

	--Get position of first delimiter
	SET @position = CHARINDEX(@delimiter,@text)
	SET @nextPosition = 1

	--Loop while there is still a delimiter in the string of levels
	WHILE (@position <>  0 AND @delimiterCount < @index )  
	BEGIN
		SET @nextString = SUBSTRING(@text,1,@position - 1)
		SET @text = SUBSTRING(@text,@position +1,len(@text))
		SET @nextPosition = @position
		SET @position  = CHARINDEX(@delimiter,@text)
		SET @delimiterCount = @delimiterCount + 1
	END

	RETURN @nextString

END








 